package com.ib.review.base.utils;

import com.ib.review.base.exception.ServiceException;
import com.ib.review.base.response.StatusCode;
import com.ib.review.constant.CommonConstant;
import com.ib.review.pojo.vo.HeaderNode;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;

public class ExcelHeaderParser {

    public Map<String, List<HeaderNode>> parseExcel(MultipartFile file) throws IOException {
        Map<String, List<HeaderNode>> result = new LinkedHashMap<>();

        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                String sheetName = sheet.getSheetName();
                List<HeaderNode> headers = parseSheetHeader(sheet);
                result.put(sheetName, headers);
            }
        }

        return result;
    }

    private List<HeaderNode> parseSheetHeader(Sheet sheet) {
        // 1. 查找包含"序号"的单元格位置（支持合并单元格）
        CellPosition headerPosition = findHeaderPosition(sheet);
        if (headerPosition == null) {
            throw new ServiceException(StatusCode.NOT_FOUND_HEADER);
        }

        // 2. 根据合并行数确定要读取的行数
        List<Row> headerRows = new ArrayList<>();
        for (int i = 0; i < headerPosition.mergedRowSpan; i++) {
            Row row = sheet.getRow(headerPosition.rowNum + i);
            if (row == null) break; // 遇到空行则停止
            headerRows.add(row);
        }

        // 3. 构建表头树形结构
        return buildHeaderTree(headerRows, sheet);
    }

    // 新增方法：查找表头位置（支持合并单元格）
    private CellPosition findHeaderPosition(Sheet sheet) {
        int maxSearchRows = CommonConstant.MAX_SEARCH_ROWS;
        // 1. 首先检查合并区域
        for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
            if (mergedRegion.getFirstRow() < maxSearchRows) {
                Row row = sheet.getRow(mergedRegion.getFirstRow());
                if (row != null) {
                    Cell cell = row.getCell(mergedRegion.getFirstColumn());
                    if (cell != null && "序号".equals(getCellValueAsString(cell).trim())) {
                        return new CellPosition(
                                mergedRegion.getFirstRow(),
                                mergedRegion.getFirstColumn(),
                                mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1,
                                mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1
                        );
                    }
                }
            }
        }

        // 2. 如果没有找到合并单元格，检查普通单元格
        for (int rowNum = 0; rowNum < maxSearchRows; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) continue;

            Cell cell = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            if ("序号".equals(getCellValueAsString(cell).trim())) {
                // 普通单元格默认为1行1列
                return new CellPosition(rowNum, 0, 1, 1);
            }
        }

        return null;
    }

    // 修改后的构建表头树方法（支持合并单元格）
    private List<HeaderNode> buildHeaderTree(List<Row> headerRows, Sheet sheet) {
        List<HeaderNode> rootNodes = new ArrayList<>();
        if (headerRows.isEmpty()) return rootNodes;

        // 存储每列的节点
        Map<Integer, Map<Integer, HeaderNode>> rowColumnNodeMap = new HashMap<>();
        // 记录已处理的合并区域
        Set<CellRangeAddress> processedMergedRegions = new HashSet<>();

        // 1. 构建所有节点
        for (int rowIdx = 0; rowIdx < headerRows.size(); rowIdx++) {
            Row row = headerRows.get(rowIdx);
            Map<Integer, HeaderNode> currentRowNodes = new HashMap<>();

            for (int colIdx = 0; colIdx < row.getLastCellNum(); colIdx++) {
                // 检查是否在已处理的合并区域中
                if (isInProcessedMergedRegion(processedMergedRegions, row.getRowNum(), colIdx)) {
                    continue;
                }

                Cell cell = row.getCell(colIdx, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                String cellValue = getCellValueAsString(cell).trim();

                if (!cellValue.isEmpty()) {
                    currentRowNodes.put(colIdx, new HeaderNode(cellValue));
                    markMergedRegions(sheet, processedMergedRegions, row.getRowNum(), colIdx);
                }
            }
            rowColumnNodeMap.put(rowIdx, currentRowNodes);
        }

        // 2. 建立父子关系（新增的核心修改）
        for (int rowIdx = headerRows.size() - 1; rowIdx > 0; rowIdx--) {
            Map<Integer, HeaderNode> currentRow = rowColumnNodeMap.get(rowIdx);
            Map<Integer, HeaderNode> upperRow = rowColumnNodeMap.get(rowIdx - 1);

            for (Map.Entry<Integer, HeaderNode> entry : currentRow.entrySet()) {
                int colIdx = entry.getKey();
                HeaderNode childNode = entry.getValue();

                // 改进的父节点查找逻辑
                HeaderNode parent = findParentNode(colIdx, upperRow, rowColumnNodeMap, sheet, rowIdx - 1);
                if (parent != null && !containsChild(parent, childNode)) {
                    parent.addChild(childNode);
                    processSiblingsForMergedRegion(colIdx, parent, currentRow, sheet);
                }
            }
        }

        return new ArrayList<>(rowColumnNodeMap.get(0).values());
    }

    // 新增方法：改进的父节点查找
    private HeaderNode findParentNode(int colIdx,
                                      Map<Integer, HeaderNode> directUpperRow,
                                      Map<Integer, Map<Integer, HeaderNode>> rowColumnNodeMap,
                                      Sheet sheet,
                                      int upperRowIdx) {
        // 1. 优先查找同列的直接上方节点
        if (directUpperRow.containsKey(colIdx)) {
            return directUpperRow.get(colIdx);
        }

        // 2. 向上追溯同列的非空父节点（最多追溯4层）
        for (int i = 1; i <= 4 && upperRowIdx - i >= 0; i++) {
            Map<Integer, HeaderNode> higherRow = rowColumnNodeMap.get(upperRowIdx - i);
            if (higherRow != null && higherRow.containsKey(colIdx)) {
                return higherRow.get(colIdx);
            }
        }

        // 3. 检查合并区域
        for (CellRangeAddress region : sheet.getMergedRegions()) {
            if (region.isInRange(colIdx, colIdx)) {
                if (directUpperRow.containsKey(region.getFirstColumn())) {
                    return directUpperRow.get(region.getFirstColumn());
                }
            }
        }

        // 4. 最后检查左侧节点（最多3列）
        for (int i = 1; i <= 3 && colIdx - i >= 0; i++) {
            if (directUpperRow.containsKey(colIdx - i)) {
                return directUpperRow.get(colIdx - i);
            }
        }

        return null;
    }

    // 新增辅助方法
    private boolean isInProcessedMergedRegion(Set<CellRangeAddress> processedMergedRegions,
                                              int rowNum, int colIdx) {
        for (CellRangeAddress region : processedMergedRegions) {
            if (region.isInRange(rowNum, colIdx) && colIdx != region.getFirstColumn()) {
                return true;
            }
        }
        return false;
    }

    // 新增辅助方法
    private void markMergedRegions(Sheet sheet,
                                   Set<CellRangeAddress> processedMergedRegions,
                                   int rowNum, int colIdx) {
        for (CellRangeAddress region : sheet.getMergedRegions()) {
            if (region.isInRange(rowNum, colIdx) && region.getFirstColumn() == colIdx) {
                processedMergedRegions.add(region);
            }
        }
    }

    // 处理合并区域的同级节点
    private void processSiblingsForMergedRegion(int colIdx,
                                                HeaderNode parent,
                                                Map<Integer, HeaderNode> currentRow,
                                                Sheet sheet) {
        for (CellRangeAddress region : sheet.getMergedRegions()) {
            if (region.getFirstColumn() == colIdx) {
                // 添加合并区域右侧的节点
                for (int i = 1; i <= region.getLastColumn() - region.getFirstColumn(); i++) {
                    int siblingCol = colIdx + i;
                    if (currentRow.containsKey(siblingCol) &&
                            !containsChild(parent, currentRow.get(siblingCol))) {
                        parent.addChild(currentRow.get(siblingCol));
                    }
                }
                break;
            }
        }
    }

    // 检查是否已包含子节点
    private boolean containsChild(HeaderNode parent, HeaderNode child) {
        return parent.getChildren().stream()
                .anyMatch(c -> c.getLabel().equals(child.getLabel()));
    }

    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 防止科学计数法和浮点数问题
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}
